<?php
require_once ($_SERVER['DOCUMENT_ROOT'].'/global.php');
include_once ($_SERVER['DOCUMENT_ROOT'].'/PHPExcel/Classes/PHPExcel.php');
$objPHPExcel = new PHPExcel();
/*以下是一些设置 ，什么作者  标题啊之类的*/
$objPHPExcel->getProperties()->setCreator("www.evuan.com")
->setLastModifiedBy('DingJinBiao')
->setTitle("DingJinBiao")
->setSubject("DingJinBiao")
->setDescription("DingJinBiao")
->setKeywords("DingJinBiao")
->setCategory("DingJinBiao");
/////////////////////////////////////////////
$array = array();

function getColumnNumber($char){//根据列号获取列对应数字
	$varchar = 'A';
	for ($i=0; $i < 10000; $i++) {
		if ($varchar == $char) {
			break;
		}
		$varchar++;
	}
	return $i;
}
function get_char($char,$n){
	for ($i=0; $i < $n; $i++) {
		$char++;
	}
	return $char;
}
$objPHPExcel->setActiveSheetIndex(0)
  ->setCellValue('A1', '序号')
  ->setCellValue('B1', '姓名')
  ->setCellValue('C1', '学工号')
  ->setCellValue('D1', '读者类型');
//统计日期
$sql = " select RiQi from entry_record_inhand group by RiQi order by RiQi ";
$query = $mysqli->query($sql);
$RiQiArray = array();
$counter = 0;
while ($a = $query->fetch_assoc()) {
  $RiQiArray[] = $a;
}
$RiQiSql = "";
for ($i = 0; $i < count($RiQiArray); $i++) {
  $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue(get_char('A',$i+4).'1',$RiQiArray[$i]['RiQi']);
  $j = $i+1;
  $RiQiSql .= "0+cast(round(sum(if(RiQi = '{$RiQiArray[$i]['RiQi']}',Duration,0)),1) as char) RiQi{$j},";
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(get_char('A',$i+4).'1','合计');

$sql = "
  SELECT
    {$RiQiSql}
    0+cast(round(sum(Duration),1) as char) Duration,
    b.XingMing,
    b.XueHao,
    if(LeiBie=1,'教师','学生') LeiBie
  FROM
    entry_record_inhand a,
    user b
  WHERE
    a.XueHao = b.XueHao
  group by a.XueHao
  order by
    a.XueHao
";
$query = $mysqli->query($sql);
$jishuqi = 1;
while ($a = $query->fetch_assoc()) {
  $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A'.(string)($jishuqi+1),$jishuqi)
              ->setCellValue('B'.(string)($jishuqi+1),$a['XingMing'])
              ->setCellValue('C'.(string)($jishuqi+1),$a['XueHao'])
              ->setCellValue('D'.(string)($jishuqi+1),$a['LeiBie']) ;
  for ($i = 0; $i < count($RiQiArray); $i++) {
      $j = $i+1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(get_char('A',$i+4).(string)($jishuqi+1),$a['RiQi'.$j]) ;
  }
  $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue(get_char('A',$i+4).(string)($jishuqi+1),$a['Duration']) ;
  $jishuqi++;
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="手工报表.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
